const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *请款单
 */

//  获得订单信息
const getInvoiceOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = '',
        totalSql = '',

        publicSearchSql = ` AND RZ.RQ >= '${body.times[0]} 00:00:00' AND RZ.RQ <= '${body.times[1]} 23:59:59'`;
    if (body.getStyle === 'RKD') {
        // 按入库单提取
        let publicConditionSql = ` WHERE RZ.SH = 'T' AND DZ.SH = 'T' AND RZ.BMBM = '${body.BMBM}' AND RZ.DWBM = '${body.DWBM}' AND (RZ.YJS-ISNULL(QK.YQJE,0)) > 0 AND RZ.YJS>ISNULL(RZ.EJS,0) `;
        startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY RZ.ID) AS px,RZ.RQ ,RZ.ID ,RZ.ZJE,RZ.JSFSMC,RZ.JSFSBM,RZ.FKQX,
        RZ.YJS,RZ.EJS,RZ.WJS,RZ.ID_DHD AS DDID,DZ.ZJE AS DDJE,DZ.JBRMC AS CGY,DZ.RQ AS CGRQ,RZ.JBRMC,(RZ.YJS-ISNULL(QK.YQJE,0)) AS KQJE FROM RKD_ZB RZ LEFT JOIN DHD_ZB DZ ON RZ.ID_DHD = DZ.ID 
        LEFT JOIN (SELECT ID_GL,SUM(BCFK) AS YQJE FROM QKD_ZB WHERE DJLX = 'RKD' GROUP BY ID_GL) QK ON RZ.ID = QK.ID_GL ${publicConditionSql} `;
        totalSql = `SELECT COUNT(RZ.ID) AS TOTAL FROM RKD_ZB RZ LEFT JOIN DHD_ZB DZ ON RZ.ID_DHD = DZ.ID  LEFT JOIN (SELECT ID_GL,SUM(BCFK) AS YQJE FROM QKD_ZB 
        WHERE DJLX = 'RKD' GROUP BY ID_GL) QK ON RZ.ID = QK.ID_GL ${publicConditionSql} `;


    } else if (body.getStyle === 'DHD') {
        // 按订货单提取 
        let publicConditionSql = ` WHERE  RZ.SH = 'T' AND RZ.BMBM = '${body.BMBM}' AND RZ.DWBM = '${body.DWBM}' AND RZ.JSFSBM = '0' AND (RZ.YJS-ISNULL(QK.YQJE,0)) > 0 AND RZ.YJS>ISNULL(RZ.EJS,0) `;
        startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY RZ.ID) AS px,RZ.RQ ,RZ.ID ,RZ.ZJE,RZ.JSFSMC,RZ.JSFSBM,RZ.FKQX,RZ.YJS,RZ.EJS,RZ.WJS,RZ.JBRMC, (RZ.YJS-ISNULL(QK.YQJE,0)) AS KQJE 
        FROM DHD_ZB RZ  LEFT JOIN (SELECT ID_GL,SUM(BCFK) AS YQJE FROM QKD_ZB WHERE DJLX = 'DHD' GROUP BY ID_GL) QK ON RZ.ID = QK.ID_GL ${publicConditionSql} `;
        totalSql = `SELECT COUNT(RZ.ID) AS TOTAL FROM DHD_ZB RZ LEFT JOIN (SELECT ID_GL,SUM(BCFK) AS YQJE FROM QKD_ZB WHERE DJLX = 'DHD' GROUP BY ID_GL) QK ON RZ.ID = QK.ID_GL ${publicConditionSql} `


    }

    totalSql += publicSearchSql;
    startSql += `${publicSearchSql} ) AS t1 WHERE t1.px between ${(body.pageNum - 1) * body.pageSize + 1} and ${body.pageNum * body.pageSize}`;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);

    ctx.body = {
        result: result.recordset,
        total: total.recordset[0].TOTAL,
    };
}


//  获得购退订单信息
const getInvoiceThdOrder = async (ctx, next) => {
    const body = ctx.request.body;

    let publicSearchSql = ` AND RZ.RQ >= '${body.times[0]} 00:00:00' AND RZ.RQ <= '${body.times[1]} 23:59:59'`;
    let publicConditionSql = ` FROM THD_ZB RZ  WHERE  RZ.SH = 'T' AND RZ.WJS>0 AND RZ.ID NOT IN ( SELECT ID_GL FROM QKD_ZB WHERE DJLX = 'THD')  
    AND RZ.BMBM = '${body.BMBM}' AND RZ.DWBM = '${body.DWBM}' `;
    // 按退货单提取
    let startGtdSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY RZ.ID) AS px,RZ.RQ ,RZ.ID ,RZ.ZSL,RZ.ZJE,RZ.JSFSMC,RZ.JSFSBM,RZ.SKQX AS FKQX,
                    RZ.YJS,RZ.EJS,RZ.WJS,RZ.JBRMC ${publicConditionSql} `;
    let totalGtdSql = `SELECT COUNT(RZ.ID) AS TOTAL ${publicConditionSql} `
    startGtdSql += ` ${publicSearchSql} ) AS t1 WHERE t1.px between ${(body.thdPageNum - 1) * 5 + 1} and ${body.thdPageNum * 5} `;
    totalGtdSql += publicSearchSql;

    let thdActionResult = await sql.query(startGtdSql);
    let thdActionTotal = await sql.query(totalGtdSql);

    ctx.body = {
        thdResult: thdActionResult.recordset,
        thdTotal: thdActionTotal.recordset[0].TOTAL
    };
}



//  获得订单对应的商品信息
const getInvoiceShop = async (ctx, next) => {
    const body = ctx.request.body;
    let nowTime = baseFn.getNowTimeId().halfDate;
    let startSql = '';
    if (body.style === 'RKD') {
        startSql = `SELECT RM.ID ,RM.YPBM,RM.YPMC,RM.SCPH,RM.YPGG,RM.JLDW,RM.SCCJ,RM.PZWH,RM.SL,RM.DJ,RM.JE ,DM.SL AS DDSL,DM.DJ AS DDDJ,DM.JE AS DDJE,FZ.FLFS,FZ.JSFS,
        FZ.FLBL,FZ.FLKSRQ,FZ.FLJSRQ,FZ.FLJS,FZ.JJRQ,FZ.YJTS,FZ.QYZT,FZ.TDZ,FZ.PPFL,FZ.FLTJ,TH.THDJ,TH.THSL,TH.THJE FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID 
        LEFT JOIN DHD_MX DM ON RM.YPBM = DM.YPBM AND RZ.ID_DHD = DM.ID LEFT JOIN (SELECT * FROM FLSZ WHERE FLKSRQ <= '${nowTime}' AND FLJSRQ >= '${nowTime}') FZ ON RZ.DWBM = FZ.GYSBM AND RM.YPBM = FZ.YPBM LEFT JOIN 
        (SELECT TM.ID_RKD,TM.YPBM,TM.SCPH,TM.DJ AS THDJ,SUM(TM.SL) AS THSL,SUM(TM.JE) AS THJE FROM THD_MX TM WHERE ID_RKD IS NOT NULL GROUP BY 
        ID_RKD,YPBM,SCPH,DJ  ) TH ON RM.ID = TH.ID_RKD AND RM.YPBM = TH.YPBM AND RM.SCPH = RM.SCPH WHERE RZ.ID = '${body.orderId}' `;

    } else if (body.style === 'DHD') {
        startSql = `SELECT RM.ID ,RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.SCCJ,RM.PZWH,RM.SL,RM.DJ,RM.JE,FZ.FLFS,FZ.JSFS,FZ.FLBL,FZ.FLKSRQ,FZ.FLJSRQ,FZ.FLJS,
        FZ.JJRQ,FZ.YJTS,FZ.QYZT,FZ.TDZ,FZ.PPFL,FZ.FLTJ  FROM DHD_MX RM LEFT JOIN DHD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN (SELECT * FROM FLSZ WHERE FLKSRQ <= '${nowTime}' 
        AND FLJSRQ >= '${nowTime}' AND FLJS != '现返') FZ ON RZ.DWBM = FZ.GYSBM AND RM.YPBM = FZ.YPBM WHERE RM.ID = '${body.orderId}'`;

    } else if (body.style === 'THD') {
        startSql = `SELECT RM.ID ,RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.SCCJ,RM.PZWH,RM.SL,RM.DJ,RM.JE  FROM THD_MX RM WHERE RM.ID = '${body.orderId}'`;

    };
    let result = await sql.query(startSql);
    ctx.body = {
        data: result.recordset
    };
}


const createId = ({ userId, oldOrder, ind }) => {
    let newSerialNum = ind;
    if (oldOrder.recordset.length !== 0) {
        let oldOrderId = oldOrder.recordset[0].ID;
        let oldSerialNum = oldOrderId.slice(oldOrderId.length - 4);
        newSerialNum = Number(oldSerialNum) + ind;
    }
    newSerialNum = newSerialNum.toString();
    newSerialNum = '0000'.slice(0, 4 - newSerialNum.length) + newSerialNum;
    return baseFn.getNowTimeId().date + userId + '12' + newSerialNum;
}



// 保存请款单的信息
const keepInvoiceInfor = async (ctx, next) => {

    try {
        let getBody = ctx.request.body;
        let orderList = getBody.orderList;
        let queryList = getBody.queryList;

        // 生成请款单id
        let lastOrderSql = `SELECT TOP 1 ID FROM QKD_ZB WHERE ID LIKE '${baseFn.getNowTimeId().date}${queryList.userId}12%' ORDER BY ID DESC`;
        let oldOrder = await sql.query(lastOrderSql);

        let insertSqlList = [];

        // 生成付款单ID
        let fkdId = await baseFn.createId(queryList.userId, 'FKD_ZB');

        // let fkdZje = 0;
        let thdJe = 0;
        let fkdTje = 0;
        let fkdYfe = 0;
        // let fkdEjs = 0;
        // let fkdWjs = 0;

        //插入付款单明细
        let insertFkdMxSql = `INSERT INTO [dbo].[FKD_MX]([ID], [XH], [NB], [DJLX], [DJID], [DJRQ],  [YJS], [EJS], [WJS],  [JBRMC], [ZDR], [SHR]) `;
        orderList.forEach((item, ind) => {
            let newId = createId({ userId: queryList.userId, ind: ind + 1, oldOrder });
            if (item.DJLX === 'RKD') {
                let updateThdZbSql = ` UPDATE THD_ZB SET EJS = YJS,WJS = 0 WHERE ID IN (SELECT ID FROM THD_MX WHERE ID_RKD = '${item.ID}') `;
                insertSqlList.push(updateThdZbSql);
            }
            let zje = Number(item.ZJE ? item.ZJE : 0);
            let yjs = Number(item.YJS ? item.YJS : 0);
            let bcfk = Number(item.BCFK ? item.BCFK : 0);
            let ejs = Number(item.EJS ? item.EJS : 0) + bcfk;
            let tje = Number(item.TJJE ? item.TJJE : 0);
            let wjs = yjs - ejs - tje;

            // fkdZje += zje;
            fkdTje += tje;
            fkdYfe += bcfk;
            if (item.DJLX === 'THD') {
                thdJe += zje;
            }

            // fkdEjs += ejs;
            // fkdWjs += wjs;

            let fkdSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);
            // 插入付款单明细
            if (item.DJLX == 'THD') {
                insertFkdMxSql += ` SELECT '${fkdId}',${baseFn.getNowTimeId().msDate + fkdSerialNum},${ind + 1},'${item.DJLX}','${item.ID}','${item.RQ}'
                ,${yjs},${ejs},${wjs},'${item.JBRMC}','${item.JBRMC}','${item.JBRMC}' `;
                if (ind !== (orderList.length - 1)) {
                    insertFkdMxSql += ` UNION `;
                }
            } else {
                insertFkdMxSql += ` SELECT '${fkdId}',${baseFn.getNowTimeId().msDate + fkdSerialNum},${ind + 1},'${item.DJLX}','${item.ID}','${item.RQ}'
                ,${bcfk - tje},0,${bcfk - tje},'${item.JBRMC}','${item.JBRMC}','${item.JBRMC}' `;
                if (ind !== (orderList.length - 1)) {
                    insertFkdMxSql += ` UNION `;
                }
            }

            // 更新对应单据类型的总表
            // let updateZbSql = `UPDATE ${item.DJLX}_ZB SET EJS = ${ejs},WJS = ${wjs} WHERE ID = '${item.ID}'`;

            // 插入请款单总表
            let insertZbSql = `INSERT INTO QKD_ZB(ID,RQ,DWBM,DWMC,BMBM,BMMC,JBRBM,JBRMC,QKR,FPH,YJS,EJS,WJS,TJE,BCFK,FLJE,ID_GL,ID_FKD,DJLX,ZFFS,JLSP,CWSP,ZJLSP,CWHX,BZ) VALUES('${newId}',
            '${baseFn.getNowTimeId().fullDate}','${queryList.GYSBM}','${queryList.GYS}','${queryList.BMBM}','${queryList.BMMC}','${queryList.userId}'
            ,'${queryList.username}','${queryList.QKRMC}','${item.FPH ? item.FPH : ''}',${yjs},${ejs},${wjs},${tje},${bcfk},${item.FLJE ? item.FLJE : 0},'${item.ID}','${fkdId}',
            '${item.DJLX}','${queryList.ZFFS}','F','F','F','F','${item.BZ ? item.BZ : ''}')`;


            // insertSqlList.push(updateZbSql);
            insertSqlList.push(insertZbSql);


            // 插入请款单明细表
            let insertQkd_MxSql = `INSERT INTO QKD_MX(ID,XH,NB,YPBM,YPMC,YPGG,SCCJ,PZWH,SCPH,DJ,SL,JE,JLDW,KPDJ,FLFS,FLQX,PPFL,FLTJ,FLJE) `;

            item.shopList.forEach((shop, shopInd) => {

                let xhSerialNum = '00000'.slice(0, 5 - (shopInd + 1).toString().length) + (shopInd + 1);
                insertQkd_MxSql += ` SELECT  '${newId}',${baseFn.getNowTimeId().msDate + xhSerialNum},${shopInd + 1},'${shop.YPBM}','${shop.YPMC}','${shop.YPGG}',
                '${shop.SCCJ}','${shop.PZWH}','${shop.SCPH ? shop.SCPH : 'NULL'}',${shop.DJ},${shop.SL},${shop.JE},'${shop.JLDW}',${shop.KPDJ ? shop.KPDJ : 'NULL'},'${shop.FLFS ? shop.FLFS : ''}','${shop.FLQX ? shop.FLQX : ''}','${shop.PPFL ? shop.PPFL : ''}', '${shop.FLTJ ? shop.FLTJ : ''}',${shop.FLJE ? shop.FLJE : 0}`;
                if (shopInd !== (item.shopList.length - 1)) {
                    insertQkd_MxSql += ` UNION `;
                }
            })
            insertSqlList.push(insertQkd_MxSql);



        });

        insertSqlList.push(insertFkdMxSql);

        let jsfsInfoResult = await sql.query(`SELECT KM1BM,KMBM,KMQC FROM BM_JSFS WHERE BM = '${queryList.ZFFS}'`);
        let jsfsInfo = jsfsInfoResult.recordset[0];
        //插入付款单总表
        let fkdZje = fkdYfe - fkdTje - thdJe;
        let fkdWjs = orderList[0].DJLX === 'DHD' && orderList[0].JSFSBM === '0' ? fkdZje : 0;
        // 插入付款结算表
        let insertFkdJsSql = `INSERT INTO [dbo].[FKD_JS]([ID], [XH], [BM], [MC], [KM1BM], [KMBM], [KMQC], [JSJE]) 
        VALUES ('${fkdId}', 1, '${queryList.ZFFS}', '${queryList.ZFFSMC}', '${jsfsInfo.KM1BM}', '${jsfsInfo.KMBM}', 
        '${jsfsInfo.KMQC}',${fkdZje} )`;


        let insertFkdZbSql = `INSERT INTO [dbo].[FKD_ZB]([ID], [RQ], [DWBM], [DWMC], [BMBM], [BMMC], [JBRBM], [JBRMC], [ZDR], [SHR],  [JSFSBM], 
        [JSFSMC], [ZJE], [TJE], [YFE],   [WJS], [SH]) VALUES ('${fkdId}', '${baseFn.getNowTimeId().fullDate}'
        ,'${queryList.GYSBM}','${queryList.GYS}','${queryList.BMBM}','${queryList.BMMC}','${queryList.userId}','${queryList.username}', 
        '${queryList.username}', '${queryList.username}',  '${queryList.ZFFS}', '${queryList.ZFFSMC}', ${fkdZje}, ${fkdTje},
        ${fkdYfe},   ${fkdWjs}, 'F')`;

        insertSqlList.push(insertFkdJsSql);
        insertSqlList.push(insertFkdZbSql);


        // 几个表同时插入，产生错误就回滚
        let lastSql = `SET XACT_ABORT ON Begin Tran ${insertSqlList.join(' ')} `;
        // if (orderList[0].DJLX === 'DHD' && orderList[0].JSFSBM === '0') {
        //     let dwInfoSql = `SELECT YUFYE,WLYE FROM BM_WLDW WHERE BM =  '${queryList.GYSBM}'`;
        //     let dwInfoResult = await sql.query(dwInfoSql);
        //     let getYufye = dwInfoResult.recordset[0].YUFYE;
        //     let getWlye = dwInfoResult.recordset[0].WLYE;
        //     let yufye = Number(getYufye ? getYufye : 0) + fkdYfe - fkdTje;
        //     let wlye = Number(getWlye ? getWlye : 0) + fkdYfe - fkdTje;
        //     lastSql += `UPDATE BM_WLDW SET YUFYE = ${yufye},WLYE = ${wlye} WHERE BM =  '${queryList.GYSBM}' `;
        // }
        lastSql += ` Commit Tran `;
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[1] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }
    } catch (err) {
        ctx.body = { success: false, err }
    }
}


module.exports = {
    'POST /getInvoiceOrder': getInvoiceOrder,
    'POST /getInvoiceShop': getInvoiceShop,
    'POST /keepInvoiceInfor': keepInvoiceInfor,
    'POST /getInvoiceThdOrder': getInvoiceThdOrder

}
